using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.Data.Extensions;


using MvcCms.Data;
using Devart.Data.PostgreSql;

namespace MvcCmsSocial.Data
{
    public class EntitySocialRepository : ISocialRepository
    {
        #region Constructors
        private readonly MvcCmsEntities _entities;

        public EntitySocialRepository(IEntitiesProvider entitiesProvider)
        {
            _entities = entitiesProvider.GetEntityContext();
        }
        #endregion
        public User GetUser(string userName)
        {
            return (from s in _entities.UserSet
                        .Include("SocialImages")
                        .Include("Portal")
                        .Include("SocialData")
                        .Include("ContentPagesContributor")
                        .Include("ContentPagesContributor.ContentCategory")
                    where s.UserName.ToLower() == userName.ToLower()
                    select s).FirstOrDefault();
        }

        #region Message methods
        public DTOSocialMessage CreateSocialMessage(SocialMessage socialMessageToCreate)
        {
            socialMessageToCreate.MessageType = GetSocialMessageType(socialMessageToCreate.MessageType.Name);
            int? parentMessageId = null;
            if (socialMessageToCreate.ParentMessage.MessageID != 0)
            {
                parentMessageId = socialMessageToCreate.ParentMessage.MessageID;
            }

            DbCommand command = _entities.CreateStoreCommand("insert_SocialMessage",
                CommandType.StoredProcedure,
                new PgSqlParameter("_FromUserID", socialMessageToCreate.FromUser.UserID),
                new PgSqlParameter("_ToUserID", socialMessageToCreate.ToUser.UserID),
                new PgSqlParameter("_Body", socialMessageToCreate.Body),
                new PgSqlParameter("_MessageTypeName", socialMessageToCreate.MessageType.Name),
                new PgSqlParameter("_ParentMessageID", parentMessageId)
                );
            using (command.Connection.CreateConnectionScope())
            {
                using (DbDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    object resourceid = reader.GetValue(0);
                    var newId = int.Parse(resourceid.ToString());
                    return GetDTOSocialMessage(newId);
                }                
            }                          
        }
        private SocialMessageType GetSocialMessageType(string name)
        {
            return (from s in _entities.SocialMessageTypeSet
                    where s.Name.ToLower() == name.ToLower() 
                    select s).FirstOrDefault();
        }
        private DTOSocialMessage GetDTOSocialMessage(int messageId)
        {
            return (from s in _entities.DTOSocialMessageSet 
                    where s.MessageID == messageId
                    select s).FirstOrDefault();
        }
        public SocialMessage EditSocialMessage(SocialMessage socialMessageToEdit)
        {
            // Save changes
            _entities.SocialMessageSet.ApplyCurrentValues(socialMessageToEdit);
            _entities.SaveChanges();
            return socialMessageToEdit;
        }
        public SocialMessage GetSocialMessage(int messageId)
        {
            return (from s in _entities.SocialMessageSet
                    where s.MessageID == messageId 
                    select s).FirstOrDefault();
        }
        public SocialImage GetProfilePic(Guid userId)
        {
            return (from s in _entities.SocialImageSet 
                    where s.User.UserID == userId &&
                    s.IsProfilePic 
                    select s).FirstOrDefault();
        }
        public IEnumerable<SocialMessage> ListSocialMessages(Guid toUserId)
        {
           return (from c in _entities.SocialMessageSet
                    .Include("FromUser")
                    .Include("Likes")
                    .Include("Likes.Liker")
                    where   c.ToUser.UserID == toUserId &&
                            c.ParentMessage.MessageID == null 
                    orderby c.AddedDate descending 
                    select c);            

        }
        public IEnumerable<DTOSocialMessage> ListSocialMessageReplies(int parentMessageId)
        {
            return (from c in _entities.DTOSocialMessageSet 
                    orderby c.AddedDate
                    where c.ParentMessageID == parentMessageId 
                    orderby c.AddedDate descending 
                    select c);
        }
        public void DeleteSocialMessage(SocialMessage socialMessageToDelete)
        {
            var originalSocialMessage = GetSocialMessage(socialMessageToDelete.MessageID);

            _entities.DeleteObject(originalSocialMessage);
            _entities.SaveChanges();
        }
        #endregion

        #region Data methods
        public SocialData CreateSocialData(SocialData socialDataToCreate)
        {
            _entities.SocialDataSet.AddObject(socialDataToCreate);
            _entities.SaveChanges();
            return socialDataToCreate;
        }
        public SocialData EditSocialData(SocialData socialDataToEdit)
        {
            _entities.SocialDataSet.ApplyCurrentValues(socialDataToEdit);
            _entities.SaveChanges();
            return socialDataToEdit;
        }
        public SocialData GetSocialData(Guid userId)
        {
            return (from s in _entities.SocialDataSet
                    where s.User.UserID == userId
                    select s).FirstOrDefault();
        }
        #endregion

        #region Image methods
        public SocialImage CreateSocialImage(SocialImage socialImageToCreate)
        {
            if (socialImageToCreate.IsProfilePic)
            {
                DbCommand command = _entities.CreateStoreCommand("set_profile_pic",
                     CommandType.StoredProcedure,
                     new PgSqlParameter("_UserId", socialImageToCreate.User.UserID),
                     new PgSqlParameter("_ImageName", socialImageToCreate.ImageName)
                    );
                using (command.Connection.CreateConnectionScope())
                {
                    command.ExecuteReader();               
                }
            }                                    
            _entities.SocialImageSet.AddObject(socialImageToCreate);                                                                        
            _entities.SaveChanges();
            return socialImageToCreate;
        }
        public void DeleteSocialImage(SocialImage socialImageToDelete)
        {
            var originalSocialImage = GetSocialImage(socialImageToDelete.ImageID);

            _entities.DeleteObject(originalSocialImage);
            _entities.SaveChanges();
        }
        public IEnumerable<SocialImage> ListSocialImages(Guid userId)
        {
            return (from c in _entities.SocialImageSet 
                    orderby c.ImageFriendlyName
                    where c.User.UserID == userId 
                    select c).ToList();
        }

        public SocialImage GetSocialImage(string imageName, Guid userId)
        {
            return (from s in _entities.SocialImageSet
                    where s.ImageName.ToLower() == imageName.ToLower() &&
                    s.User.UserID == userId 
                    select s).FirstOrDefault();
        }
        private SocialImage GetSocialImage(int imageId)
        {
            return (from s in _entities.SocialImageSet
                    where s.ImageID == imageId 
                    select s).FirstOrDefault();
        }
        public SocialImage GetSocialImage(string imageName)
        {
            return (from s in _entities.SocialImageSet
                    where s.ImageName.ToLower() == imageName.ToLower() 
                    select s).FirstOrDefault();
        } 
        public SocialImage EditSocialImage(SocialImage socialImageToEdit)
        {
            if (socialImageToEdit.IsProfilePic)
            {
                DbCommand command = _entities.CreateStoreCommand("set_profile_pic",
                     CommandType.StoredProcedure,
                     new PgSqlParameter("_UserId", socialImageToEdit.User.UserID),
                     new PgSqlParameter("_ImageName", socialImageToEdit.ImageName)
                    );
                using (command.Connection.CreateConnectionScope())
                {
                    command.ExecuteReader();
                }
            }

            // Save changes
            _entities.SocialImageSet.ApplyCurrentValues(socialImageToEdit);
            _entities.SaveChanges();
            return socialImageToEdit;
        }
        #endregion
        private SocialLike GetSocialLike(int likeId)
        {
            return (from s in _entities.SocialLikeSet 
                    where s.LikeID == likeId 
                    select s).FirstOrDefault();
        }
        public SocialLike CreateSocialLike(SocialLike socialLikeToCreate)
        {
            DbCommand command = _entities.CreateStoreCommand("insert_social_like",
                CommandType.StoredProcedure,
                new PgSqlParameter("_UserID", socialLikeToCreate.Liker.UserID),
                new PgSqlParameter("_MessageID", socialLikeToCreate.Message.MessageID)
                );
            using (command.Connection.CreateConnectionScope())
            {
                using (DbDataReader reader = command.ExecuteReader())
                {
                    reader.Read();                    
                    object likeId = reader.GetValue(0);
                    return GetSocialLike(int.Parse(likeId.ToString()));
                }
            }  
        }
    }
}